package DBLayer;

import java.sql.*;
import java.util.ArrayList;
import ModelLayer.*;

public class DBProduct implements IFDBProduct {

	private Connection con;
	private DBSupplier dbSupplier;

	public DBProduct() 
	{
		con = DBConnection.getInstance().getDBcon();
	}

	//Method is search for a product which it deletes afterwards
	public int deleteProduct(int productNumber) {
		int rc = -1;

		String query = "DELETE FROM product WHERE productNumber = '"
				+ productNumber + "'";
		System.out.println(query);
		try { // delete from product
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}// slut try
		catch (Exception ex) {
			System.out.println("Delete exception in product db: " + ex);
		}
		return (rc);
	}

	//Method is used to find a product in the DB
	public Product findProduct(int productNumber, boolean retrieveAssociation) {
		String wClause = " productNumber = '" + productNumber + "'";
		return singleWhere(wClause, retrieveAssociation);
	}

	//Method is used to list all products from the DB
	public ArrayList<Product> listAllProducts(boolean retrieveAssociation) {
		return miscWhere("", retrieveAssociation);
	}
	
	//Method is used to list all tire objects from the DB
	public ArrayList<Tire> listAllTires(boolean retrieveAssociation)
	{
		String wClause = " productType = 'Tire' ";
		return tiremiscWhere(wClause, retrieveAssociation);
	}
	
	//Method is used to list all gearbox objects from the DB
	public ArrayList<Gearbox> listAllGearboxs(boolean retrieveAssociation)
	{
		String wClause = " productType = 'Gearbox' ";
		return gearBoxmiscWhere(wClause, retrieveAssociation);
	}
	
	//Method is used to list all glass objects from the DB
	public ArrayList<Glass> listAllGlass(boolean retrieveAssociation)
	{
		String wClause = " productType = 'Glass' ";
		return glassmiscWhere(wClause, retrieveAssociation);
	}
	
	//Method is used to list all headlight objects from the DB
	public ArrayList<Headlights> listAllHeadlights(boolean retrieveAssociation)
	{
		String wClause = " productType = 'Headlight' ";
		return headlightmiscWhere(wClause, retrieveAssociation);
	}
	
	//Method is used to list all brakedisc objects from the DB
	public ArrayList<BrakeDisc> listAllBrakeDisc(boolean retrieveAssociation)
	{
		String wClause = " productType = 'Brakedisc' ";
		return brakeDiscmiscWhere(wClause, retrieveAssociation);
	}

	//Method is used to list all product objects from a given supplier
	public ArrayList<Product> listAllProductsFromSupplier(int supplierID,
			boolean retrieveAssociation) {
		return miscWhere("supplierId = " + supplierID, true);
	}

	//Method is used when updating a product
	public int updateProduct(Product pro) {
		Product theProduct = pro;

		String query = updateControl(theProduct);
		int rc = -1;
		System.out.println("Update query:" + query);
		try { // update employee
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
		}// slut try
		catch (Exception ex) {
			System.out.println("Update exception in product db: " + ex);
		}
		return (rc);
	}

	//Inserts a product after it has been build
	public int insertProduct(Product product) throws Exception { 
		
		Product theProduct = product;

		int nextProductNumber = DBGetMax
				.getMaxId("Select max(productnumber) from Product");
		nextProductNumber = nextProductNumber + 1;
		System.out.println("next productnumber = " + nextProductNumber);

		String query = controlProduct(theProduct, nextProductNumber);
		System.out.println(query);
		int rc = -1;

		try { 
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch (SQLException ex) {
			System.out.println("Product ikke oprettet");
			throw new Exception("Product is not inserted correct");
		}
		return (rc);
	}

	//Method is used when searching for a single product in the DB
	private Product singleWhere(String wClause, boolean retrieveAssociation) {

		Product proObj = new Product();
		String query = buildQuery(wClause);
		System.out.println(query);
		ResultSet results = null;

		try {
			Statement stmt = con.createStatement();
			
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
		} catch (Exception e) {
			System.out.println("Failed");
		}

		try {
			if (results.next()) {

				proObj = buildProduct(results, proObj);
				System.out.println(retrieveAssociation);
				if (retrieveAssociation) {

					int supplierId = proObj.getSupplier().getSupplierId();
					Supplier theSupplier = dbSupplier.findSupplier(supplierId,
							false);
					proObj.setSupplier(theSupplier);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return proObj;

	}

	//Method is used when searching for more than one product in the DB
	private ArrayList<Product> miscWhere(String wClause,
			boolean retrieveAssociation) {
		ResultSet results;
		ArrayList<Product> list = new ArrayList<Product>();

		String query = buildQuery(wClause);

		try { // read the employee from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while (results.next()) {
				Product productObj = new Product();
				productObj = buildProduct(results, productObj);
				list.add(productObj);

			}// end while

			stmt.close();

			if (retrieveAssociation) { // The supervisor and department is to be
										// build as well
				for (Product proObj : list) {
					int supplierID = proObj.getSupplier().getSupplierId();
					Supplier theSupplier = dbSupplier.findSupplier(supplierID,
							false);
					proObj.setSupplier(theSupplier);
					System.out.println("Supplier is seleceted");
					// here the department has to be selected as well
				}
			}// end if

		}// slut try
		catch (Exception e) {
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}
	
	// Method to list all Tires
	private ArrayList<Tire> tiremiscWhere(String wClause, boolean retriveAssociation)
	{
		ArrayList<Tire> tireList = new ArrayList<Tire>();
		ResultSet results;

		String query = buildProductTypeQuery(wClause);
		System.out.println(query);

		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while (results.next()) {
				Tire tiObj = new Tire();
				tiObj = (Tire) buildProduct(results, tiObj);
				tireList.add(tiObj);
			}
			stmt.close();
			if (retriveAssociation) {
				return null;
			}
		}
		catch (Exception e) {
			System.out.println("Query exception - Select: " + e);
		}
		return tireList;
	}
	
	// Method to list all gearbox
		private ArrayList<Gearbox> gearBoxmiscWhere(String wClause, boolean retriveAssociation)
		{
			ArrayList<Gearbox> gearList = new ArrayList<Gearbox>();
			ResultSet results;

			String query = buildProductTypeQuery(wClause);
			System.out.println(query);

			try {
				Statement stmt = con.createStatement();
				stmt.setQueryTimeout(5);
				results = stmt.executeQuery(query);

				while (results.next()) {
					Gearbox gbObj = new Gearbox();
					gbObj = (Gearbox) buildProduct(results, gbObj);
					gearList.add(gbObj);
				}
				stmt.close();
				if (retriveAssociation) {
					return null;
				}
			}
			catch (Exception e) {
				System.out.println("Query exception - Select: " + e);
			}
			return gearList;
		}
		
		// Method to list all Glass
		private ArrayList<Glass> glassmiscWhere(String wClause, boolean retriveAssociation)
		{
			ArrayList<Glass> glassList = new ArrayList<Glass>();
			ResultSet results;

			String query = buildProductTypeQuery(wClause);
			System.out.println(query);

			try {
				Statement stmt = con.createStatement();
				stmt.setQueryTimeout(5);
				results = stmt.executeQuery(query);

				while (results.next()) {
					Glass glObj = new Glass();
					glObj = (Glass) buildProduct(results, glObj);
					glassList.add(glObj);
				}
				stmt.close();
				if (retriveAssociation) {
					return null;
				}
			}
			catch (Exception e) {
				System.out.println("Query exception - Select: " + e);
			}
			return glassList;
		}
		
	// Method to list all brakedisc
	private ArrayList<BrakeDisc> brakeDiscmiscWhere(String wClause, boolean retriveAssociation)
		{
			ArrayList<BrakeDisc> brakeList = new ArrayList<BrakeDisc>();
			ResultSet results;

			String query = buildProductTypeQuery(wClause);
			System.out.println(query);

			try {
				Statement stmt = con.createStatement();
				stmt.setQueryTimeout(5);
				results = stmt.executeQuery(query);

				while (results.next()) {
					BrakeDisc bdObj = new BrakeDisc();
					bdObj = (BrakeDisc) buildProduct(results, bdObj);
					brakeList.add(bdObj);
				}
				stmt.close();
				if (retriveAssociation) {
					return null;
				}
			}
			catch (Exception e) {
				System.out.println("Query exception - Select: " + e);
			}
			return brakeList;
	}
	
// Method to list all brakedisc
private ArrayList<Headlights> headlightmiscWhere(String wClause, boolean retriveAssociation)
		{
			ArrayList<Headlights> headList = new ArrayList<Headlights>();
			ResultSet results;

			String query = buildProductTypeQuery(wClause);
			System.out.println(query);

			try {
				Statement stmt = con.createStatement();
				stmt.setQueryTimeout(5);
				results = stmt.executeQuery(query);

				while (results.next()) {
					Headlights hdObj = new Headlights();
					hdObj = (Headlights) buildProduct(results, hdObj);
					headList.add(hdObj);
				}
				stmt.close();
				if (retriveAssociation) {
					return null;
				}
			}
			catch (Exception e) {
				System.out.println("Query exception - Select: " + e);
			}
			return headList;
	}

	//Method to build the products query
	private String buildProductTypeQuery(String wClause)
	{
		String query = "SELECT * from product Where " + wClause;
		if(wClause.length() > 0)
			query = query + "and " + wClause;
		return query;
	}
	
	//This method builds the SQL query used for building the product object
	private String buildQuery(String wClause) {
		String query = "SELECT productnumber,serialnumber, supplierId, productname,purchaseprice,"
				+ "salesprice, rentprice,countryoforigin,"
				+ "fabricant,description,carmodel, modelyear,"
				+ "minstock,maxstock,producttype, gearboxtype,gearamount,tiretype,tiresize,glassheight"
				+ ",glasswidth,glassthickness,brakedisctype,lightstype FROM product";

		if (wClause.length() > 0)
			query = query + " WHERE " + wClause;

		return query;

	}

	//This method builds the product object
	private Product buildProduct(ResultSet results, Product proObj) {
		try {
			
			
			proObj = buildProductPart(results, proObj);
			String controlProduct = proObj.getProductType();

			//If a gearbox object
			if (controlProduct.equals("Gearbox")) {
				
				Gearbox gearObj = new Gearbox();
				
				gearObj.setProductNumber(proObj.getProductNumber());
				gearObj.setSerialNumber(proObj.getSerialNumber());
				gearObj.setProductName(proObj.getProductName());
				gearObj.setPurchasePrice(proObj.getPurchasePrice());
				gearObj.setSalesPrice(proObj.getSalesPrice());
				gearObj.setRentPrice(proObj.getRentPrice());
				gearObj.setCountryOfOrigin(proObj.getCountryOfOrigin());
				gearObj.setFabricant(proObj.getFabricant());
				gearObj.setDescription(proObj.getDescription());
				gearObj.setCarModel(proObj.getCarModel());
				gearObj.setModelYear(proObj.getModelYear());
				gearObj.setMinStock(proObj.getMinStock());
				gearObj.setMaxStock(proObj.getMaxStock());
				gearObj.setGearboxType(results.getString("gearboxtype"));
				gearObj.setGearAmount(results.getInt("gearAmount"));

				return gearObj;

			}

			//If a tire object
			if (controlProduct.equals("Tire")) {
				
				Tire tireObj = new Tire();

				tireObj.setProductNumber(proObj.getProductNumber());
				tireObj.setSerialNumber(proObj.getSerialNumber());
				tireObj.setProductName(proObj.getProductName());
				tireObj.setPurchasePrice(proObj.getPurchasePrice());
				tireObj.setSalesPrice(proObj.getSalesPrice());
				tireObj.setRentPrice(proObj.getRentPrice());
				tireObj.setCountryOfOrigin(proObj.getCountryOfOrigin());
				tireObj.setFabricant(proObj.getFabricant());
				tireObj.setDescription(proObj.getDescription());
				tireObj.setCarModel(proObj.getCarModel());
				tireObj.setModelYear(proObj.getModelYear());
				tireObj.setMinStock(proObj.getMinStock());
				tireObj.setMaxStock(proObj.getMaxStock());

				tireObj.setTireType(results.getString("tiretype"));
				tireObj.setTireSize(results.getString("tiresize"));
				

				return tireObj;
			}

			//If a glass object
			if (controlProduct.equals("Glass")) {
				Glass glassObj = new Glass();

				glassObj.setProductNumber(proObj.getProductNumber());
				glassObj.setSerialNumber(proObj.getSerialNumber());
				glassObj.setProductName(proObj.getProductName());
				glassObj.setPurchasePrice(proObj.getPurchasePrice());
				glassObj.setSalesPrice(proObj.getSalesPrice());
				glassObj.setRentPrice(proObj.getRentPrice());
				glassObj.setCountryOfOrigin(proObj.getCountryOfOrigin());
				glassObj.setFabricant(proObj.getFabricant());
				glassObj.setDescription(proObj.getDescription());
				glassObj.setCarModel(proObj.getCarModel());
				glassObj.setModelYear(proObj.getModelYear());
				glassObj.setMinStock(proObj.getMinStock());
				glassObj.setMaxStock(proObj.getMaxStock());
				

				glassObj.setHeight(results.getString("glassheight"));
				glassObj.setWidth(results.getString("glasswidth"));
				glassObj.setThickness(results.getString("glassthickness"));

				return glassObj;
			}

			//If a brakedisc object
			if (controlProduct.equals("Brakedisc")) {
				BrakeDisc brakeObj = new BrakeDisc();
				
				brakeObj.setProductNumber(proObj.getProductNumber());
				brakeObj.setSerialNumber(proObj.getSerialNumber());
				brakeObj.setProductName(proObj.getProductName());
				brakeObj.setPurchasePrice(proObj.getPurchasePrice());
				brakeObj.setSalesPrice(proObj.getSalesPrice());
				brakeObj.setRentPrice(proObj.getRentPrice());
				brakeObj.setCountryOfOrigin(proObj.getCountryOfOrigin());
				brakeObj.setFabricant(proObj.getFabricant());
				brakeObj.setDescription(proObj.getDescription());
				brakeObj.setCarModel(proObj.getCarModel());
				brakeObj.setModelYear(proObj.getModelYear());
				brakeObj.setMinStock(proObj.getMinStock());
				brakeObj.setMaxStock(proObj.getMaxStock());

				

				brakeObj.setDiscType(results.getString("brakeDiscType"));
				

				return brakeObj;
			}

			//If a headlight object
			if (controlProduct.equals("Headlight")) {
				Headlights lightObj = new Headlights();

				lightObj.setProductNumber(proObj.getProductNumber());
				lightObj.setSerialNumber(proObj.getSerialNumber());
				lightObj.setProductName(proObj.getProductName());
				lightObj.setPurchasePrice(proObj.getPurchasePrice());
				lightObj.setSalesPrice(proObj.getSalesPrice());
				lightObj.setRentPrice(proObj.getRentPrice());
				lightObj.setCountryOfOrigin(proObj.getCountryOfOrigin());
				lightObj.setFabricant(proObj.getFabricant());
				lightObj.setDescription(proObj.getDescription());
				lightObj.setCarModel(proObj.getCarModel());
				lightObj.setModelYear(proObj.getModelYear());
				lightObj.setMinStock(proObj.getMinStock());
				lightObj.setMaxStock(proObj.getMaxStock());
				

				lightObj.setLightsType(results.getString("lightstype"));
				

				return lightObj;
			}

		} catch (Exception e) {
			System.out.println("error in building the product object");
		}
		return null;
	}

	//This method is used to build the product parts
	private Product buildProductPart(ResultSet results, Product obj) {
		try {
			
			obj.setProductNumber(results.getInt("productnumber"));
			obj.setSerialNumber(results.getInt("serialnumber"));
			obj.setSupplier(new Supplier(results.getInt("supplierId")));
			obj.setProductName(results.getString("productname"));
			obj.setPurchasePrice(results.getDouble("purchaseprice"));
			obj.setSalesPrice(results.getDouble("salesPrice"));
			obj.setRentPrice(results.getDouble("rentprice"));
			obj.setCountryOfOrigin(results.getString("countryoforigin"));
			obj.setFabricant(results.getString("fabricant"));
			obj.setDescription(results.getString("description"));
			obj.setCarModel(results.getString("carmodel"));
			obj.setModelYear(results.getInt("modelyear"));
			obj.setMinStock(results.getInt("minstock"));
			obj.setMaxStock(results.getInt("maxstock"));
			obj.setProductType(results.getString("producttype"));

			return obj;
		}

		catch (Exception e) {
			System.out.println("lallalalala");
		}
		return obj;

	}

	//Used for checking the product type
	private String controlProduct(Product product, int nextProductNumber) {
		String productType = null;
		productType = product.getProductType();
		String query = null;

		query = addProduct(product, nextProductNumber);

		if (productType.equals("Gearbox")) {
			Gearbox gearObj = new Gearbox();
			gearObj = (Gearbox) product;
			query = query + ",'" + gearObj.getGearboxType() + "',"
					+ gearObj.getGearAmount()
					+ ", null, null, null, null, null, null, null, '"
					+ gearObj.getProductType() + "')";

		}

		if (productType.equals("Tire")) {
			Tire tireObj = new Tire();
			tireObj = (Tire) product;
			query = query + ", null, null,'" + tireObj.getTireType() + "','"
					+ tireObj.getTireSize()
					+ "', null, null, null, null, null, '"
					+ tireObj.getProductType() + "')";

		}
		if (productType.equals("Glass")) {
			Glass glassObj = new Glass();
			glassObj = (Glass) product;
			query = query + ", null, null, null, null,'" + glassObj.getHeight()
					+ "','" + glassObj.getWidth() + "','"
					+ glassObj.getThickness() + "', null, null, '"
					+ glassObj.getProductType() + "')";

		}

		if (productType.equals("Brakedisc")) {
			BrakeDisc brakeObj = new BrakeDisc();
			brakeObj = (BrakeDisc) product;
			query = query + ", null, null, null, null, null, null, null,'"
					+ brakeObj.getDiscType() + "', null, '"
					+ brakeObj.getProductType() + "')";

		}

		if (productType.equals("Headlight")){
			Headlights lightObj = new Headlights();
			lightObj = (Headlights) product;
			query = query
					+ ", null, null, null, null, null, null, null, null,'"
					+ lightObj.getLightsType() + "','" + "', '"
					+ lightObj.getProductType() + "')";

		}
		return query;
	}

	//Adds the product in the DB
	private String addProduct(Product product, int nextProductNumber) {

		String query = "INSERT INTO Product" + " VALUES(" + nextProductNumber
				+ "," + product.getSerialNumber() + ","
				+ product.getSupplier().getSupplierId() + ",'"
				+ product.getProductName() + "'," + product.getPurchasePrice()
				+ "," + product.getSalesPrice() + "," + product.getRentPrice()
				+ ",'" + product.getCountryOfOrigin() + "','"
				+ product.getFabricant() + "','" + product.getDescription()
				+ "','" + product.getCarModel() + "'," + product.getModelYear()
				+ "," + product.getMinStock() + "," + product.getMaxStock();

		return query;

	}

	//This method is used for checking the product type when updating the product object
	private String updateControl(Product product) {

		String productType = null;
		productType = product.getProductType();
		String query = null;

		query = makeQuery(product);

		if (productType.equals("Gearbox")) {
			Gearbox gearObj = new Gearbox();
			gearObj = (Gearbox) product;
			query = query + " gearboxtype = '" + gearObj.getGearboxType()
					+ "', " + "gearAmount = '" + gearObj.getGearAmount()
					+ "' where productNumber = " + gearObj.getProductNumber();
		}

		if (productType.equals("Tire")) {
			Tire tireObj = new Tire();
			tireObj = (Tire) product;
			query = query + "tiretype = '" + tireObj.getTireType() + "', "
					+ "tireSize = '" + tireObj.getTireSize() + "'" + " where productnumber = " + tireObj.getProductNumber();
		}

		if (productType.equals("Glass")) {
			Glass glassObj = new Glass();
			glassObj = (Glass) product;
			query = query + "glassheight = '" + glassObj.getHeight() + "', "
					+ "glassWidth = '" + glassObj.getWidth() + "', "
					+ "glassThickness = '" + glassObj.getThickness() + "'" + " where productnumber = " + glassObj.getProductNumber();
		}

		if (productType.equals("Brakedisc")) {
			BrakeDisc brakeObj = new BrakeDisc();
			brakeObj = (BrakeDisc) product;
			query = query + "brakedisctype = '" + brakeObj.getDiscType() + "'" + " where productnumber = " + brakeObj.getProductNumber();
		}

		if (productType.equals("Headlight")) {
			Headlights lightObj = new Headlights();
			lightObj = (Headlights) product;
			query = query + "lightsType = '" + lightObj.getLightsType() + "'" + " where productnumber = " + lightObj.getProductNumber();
		}
		return query;

	}

	//Makes the product SQL query
	private String makeQuery(Product product) {
		String query = "UPDATE product SET " +  "serialNumber ='"
				+ product.getSerialNumber() + "', " + "supplierId ='"
				+ product.getSupplier().getSupplierId() + "', "
				+ " productname='" + product.getProductName() + "', "
				+ "purchaseprice ='" + product.getPurchasePrice() + "', "
				+ "salesPrice ='" + product.getSalesPrice() + "', "
				+ "rentPrice ='" + product.getRentPrice() + "', "
				+ "countryOforigin ='" + product.getCountryOfOrigin() + "', "
				+ "fabricant ='" + product.getFabricant() + "', "
				+ "description ='" + product.getDescription() + "', "
				+ "carModel ='" + product.getCarModel() + "', "
				+ "modelYear ='" + product.getModelYear() + "', "
				+ "minstock ='" + product.getMinStock() + "', " + "maxstock ='"
				+ product.getMaxStock() + "', ";

		return query;
	}

}